﻿Public Class clsDocGia
    Public Shared Function DSDocGia() As DataTable
        Return db.QueryTable("Select MaDG,TenDG,NgaySinh,GioiTinh,DiaChi,NgayTao,NgayHetHan,TenDTUT,CMND from TheDocGia t inner join DTUT d on t.MaDTUT = d.MaDTUT")

    End Function
    Public Shared Function Them(ByVal MaDG As String, _
                                ByVal TenDG As String, _
                                 ByVal NgaySinh As Date, _
                                 ByVal GioiTinh As String, _
                                 ByVal DiaChi As String, _
                                 ByVal NgayTao As Date, _
                                 ByVal NgayHetHan As Date, _
                                 ByVal MaDTUT As String, _
                                 ByVal CMND As String)
        Return db.Query("insert into TheDocGia values ('" & MaDG _
                                        & "',N'" & TenDG _
                                        & "','" & NgaySinh.ToString("MM/dd/yyyy") _
                                        & "',N'" & GioiTinh _
                                        & "',N'" & DiaChi _
                                        & "','" & NgayTao.ToString("MM/dd/yyyy") _
                                        & "','" & NgayHetHan.ToString("MM/dd/yyyy") _
                                        & "','" & MaDTUT _
                                        & "','" & CMND & "')")
    End Function
    Public Shared Function Sua(ByVal MaDG As String, ByVal TenDG As String, ByVal NgaySinh As Date, ByVal GioiTinh As String, ByVal DiaChi As String, ByVal NgayTao As Date, ByVal NgayHetHan As Date, ByVal MaDTUT As String, ByVal CMND As String) As Boolean

        Return db.Query("Update TheDocGia set TenDG=N'" & TenDG & "',NgaySinh='" & NgaySinh.ToString("MM/dd/yyyy") & "',GioiTinh=N'" & GioiTinh & "',DiaChi=N'" & DiaChi & "',NgayTao='" & NgayTao.ToString("MM/dd/yyyy") & "',NgayHetHan='" & NgayHetHan.ToString("MM/dd/yyyy") & "',MaDTUT='" & MaDTUT & "',CMND='" & CMND & "' Where MaDG = '" & MaDG & "'")
    End Function
    Public Shared Function Xoa(ByVal MaDG As String) As Boolean
        Return db.Query("Delete  from TheDocGia where MaDG ='" & MaDG & "'")
    End Function
    Public Shared Function TimKiem(ByVal TuKhoa As String) As DataTable
        Return db.QueryTable("Select MaDG,TenDG,NgaySinh,GioiTinh,DiaChi,NgayTao,NgayHetHan,TenDTUT,CMND from TheDocGia t inner join DTUT d on t.MaDTUT=d.MaDTUT where MaDG like '%" & TuKhoa & "%'or TenDG like N'%" & TuKhoa & "%'or GioiTinh like N'%" & TuKhoa & "%'or DiaChi like N'%" & TuKhoa & "%' or NgaySinh like '%" & TuKhoa & "%'or NgayTao like '%" & TuKhoa & "%'or NgayHetHan like '%" & TuKhoa & "%' or CMND like '%" & TuKhoa & "%' or TenDTUT like N'%" & TuKhoa & "%'")

    End Function
    Public Shared Function PhanLoaiDG(ByVal MaDTUT As String) As DataTable
        Return db.QueryTable("Select MaDG,TenDG,NgaySinh,GioiTinh,DiaChi,NgayTao,NgayHetHan,TenDTUT from TheDocGia t inner join DTUT d on t.MaDTUT=d.MaDTUT where t.MaDTUT ='" & MaDTUT & "'")
    End Function
    ''' <summary>
    ''' Tra ve true neu ton tai doc gia
    ''' </summary>
    ''' <param name="MaDG"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function ThongTinDG(ByVal MaDG As String) As DataTable

        Return db.QueryTable("Select * from TheDocGia where MaDG='" & MaDG & "'")

    End Function
    Public Shared Function ConNoSach(ByVal MaDG As String) As Boolean
        DIm dt as new DataTable
        dt = db.QueryTable("select * from PhieuMuonChiTiet pmct inner join PhieuMuon pm on pmct.MaPM=pm.MaPM inner join TheDocGia t on t.MaDG=pm.MaDG where pm.MaDG='" & MaDG & "' and NgayTra is NULL")
        Return dt.Rows.Count > 0
    End Function
End Class
